﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class ThuChiDAO
    {
         #region 1. Insert

        public static bool InsertPhieu(PhieuThuChiDTO phieutc)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ngay", phieutc.Ngay));
                sqlParams.Add(new SqlParameter(@"NguoiThucHien",phieutc.Nguoithuchien));
                sqlParams.Add(new SqlParameter(@"DiaChi",phieutc.Diachi));
                sqlParams.Add(new SqlParameter(@"DienThoai",phieutc.Dienthoai));
                sqlParams.Add(new SqlParameter(@"NoiDung",phieutc.Noidung));
                sqlParams.Add(new SqlParameter(@"SoTien",phieutc.Sotien));
                sqlParams.Add(new SqlParameter(@"Loai",phieutc.Loai));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertPhieu", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }       

        #endregion

        #region 2. Update

        public static bool UpdatePhieu(PhieuThuChiDTO phieutc)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"Ma", phieutc.Ma));
                sqlParams.Add(new SqlParameter(@"Ngay", phieutc.Ngay));
                sqlParams.Add(new SqlParameter(@"NguoiThucHien", phieutc.Nguoithuchien));
                sqlParams.Add(new SqlParameter(@"DiaChi", phieutc.Diachi));
                sqlParams.Add(new SqlParameter(@"DienThoai", phieutc.Dienthoai));
                sqlParams.Add(new SqlParameter(@"NoiDung", phieutc.Noidung));
                sqlParams.Add(new SqlParameter(@"SoTien", phieutc.Sotien));
                sqlParams.Add(new SqlParameter(@"Loai", phieutc.Loai));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_UpdatePhieu", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }       

        #endregion
             
        #region 3. Retrive

        public static List<PhieuThuChiDTO> SelectPhieuByAll()
        { 
            List<PhieuThuChiDTO> list = new List<PhieuThuChiDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectPhieusAll", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    PhieuThuChiDTO ptc = new PhieuThuChiDTO();
                    ptc.Ma = int.Parse(dr["Ma"].ToString());
                    ptc.Nguoithuchien = dr["NguoiThucHien"].ToString();
                    if (dr["Loai"].ToString() == "1")
                        ptc.TenLoai = "Thu";
                    else
                        ptc.TenLoai = "Chi";
                    ptc.Loai = int.Parse(dr["Loai"].ToString());
                    ptc.Diachi = dr["DiaChi"].ToString();                    
                    ptc.Dienthoai = dr["DienThoai"].ToString(); 
                    ptc.Noidung = dr["NoiDung"].ToString();
                    ptc.Sotien = float.Parse(dr["SoTien"].ToString());
                    ptc.Ngay = DateTime.Parse(dr["Ngay"].ToString());
                    list.Add(ptc);
                }  
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static List<PhieuThuChiDTO> SelectPhieuByNgayDauNgayCuoi(DateTime ngaydau, DateTime ngaycuoi)
        {
            List<PhieuThuChiDTO> list = new List<PhieuThuChiDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter(@"NgayDau", ngaydau));
                sqlParams.Add(new SqlParameter(@"NgayCuoi", ngaycuoi));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectPhieuByNgayDauNgayCuoi", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    PhieuThuChiDTO ptc = new PhieuThuChiDTO();
                    ptc.Ma = int.Parse(dr["Ma"].ToString());
                    ptc.Nguoithuchien = dr["NguoiThucHien"].ToString();
                    if (dr["Loai"].ToString() == "1")
                        ptc.TenLoai = "Thu";
                    else
                        ptc.TenLoai = "Chi";
                    ptc.Loai = int.Parse(dr["Loai"].ToString());
                    ptc.Diachi = dr["DiaChi"].ToString();
                    ptc.Dienthoai = dr["DienThoai"].ToString();
                    ptc.Noidung = dr["NoiDung"].ToString();
                    ptc.Sotien = float.Parse(dr["SoTien"].ToString());
                    ptc.Ngay = DateTime.Parse(dr["Ngay"].ToString());
                    list.Add(ptc);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static int GetMaxMa()
        {
            DataTable dt = new DataTable();
            try
            {
                // Create List Sql Parameter                
                dt = SqlDataAccessHelper.ExecuteQuery("usp_GetMaPhieuThuChiMax");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (!dt.Rows[0]["Max"].Equals(System.DBNull.Value))
                return int.Parse(dt.Rows[0]["Max"].ToString()) + 1;
            else
                return 1;
        }

        #endregion
    }
}
